﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Ccit.ClassLibrary;
using Ccit.Utils;
using Ccit.SysPlat.Log;
using System.Data;
using System.Text;

namespace Ccit.SysPlat.Organ
{
    /// <summary>
    /// Description: 系统平台组织机构 -> 职位管理SQL SERVER数据实现层
    /// Author: tim 2010-02-23
    /// </summary>
    public class PosSQL : IPos
    {
        #region init error log interface
        private ILog _logInterface = null;
        private const string _funcPageName = "职位管理";
        public PosSQL()
        {
            _logInterface = Factory.CreateObject(MsErrLog.assemblyName, MsErrLog.className, 
                                                    ImportComponentType.DotNetDll, null) as ILog;
        }
        private string _multiDeptSign = HttpContext.Current.Application["MultiDept"].ToString();  //多部门多职位系统开关
        #endregion
        
        #region IPos 成员

        public List<MsKeyValue> GetPosListByIds(string posIds,string findPosName)
        {
            if (string.IsNullOrEmpty(posIds)) return new List<MsKeyValue>();

            string cmdText = "SELECT SYS_A3_10 AS POSID,SYS_A3_20 AS POSNAME FROM SYS_A3 WHERE SYS_A3_10 IN (" + posIds + ")";

            if (!string.IsNullOrEmpty(findPosName))
                cmdText += " AND SYS_A3_20 LIKE '%" + findPosName + "%'";

            List<MsKeyValue> lstPos = new List<MsKeyValue>();
            try {
                MsKeyValue msPos;
                using (IDataReader iReader = DataAccessor.ExecuteReader(cmdText))
                {
                    while (iReader.Read())
                    {
                        msPos = new MsKeyValue {
                            key = iReader["POSID"].ToString(),
                            val = iReader["POSNAME"].ToString()
                        };
                        lstPos.Add(msPos);
                    }
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + cmdText + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }
            return lstPos;
        }

        public List<MsKeyValue> GetPosListByOrgan(string organId, string filterPosIds,string findPosName)
        {
            StringBuilder sbCmdText = new StringBuilder();
            string whereClause = string.Empty;
            if (!string.IsNullOrEmpty(filterPosIds)) {
                whereClause = " AND A.SYS_A3_10 NOT IN (" + filterPosIds + ")";
            }

            if (!string.IsNullOrEmpty(findPosName)) {
                whereClause += " AND A.SYS_A3_20 LIKE '%" + findPosName + "%'";
            }

            sbCmdText.Append("SELECT A.SYS_A3_10 AS POSID,A.SYS_A3_20 AS POSNAME ");
            sbCmdText.Append("FROM SYS_A3 A INNER JOIN SYS_R6 B ON A.SYS_A3_10=B.SYS_R6_20 ");
            sbCmdText.Append("WHERE B.SYS_R6_10='").Append(organId).Append("' AND A.SYS_A3_60='0'").Append(whereClause);
            sbCmdText.Append(" ORDER BY A.SYS_A3_50 ASC");
            List<MsKeyValue> lstPos = new List<MsKeyValue>();
            try
            {
                MsKeyValue msPos = null;
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        msPos = new MsKeyValue
                        {
                            key = iReader["POSID"].ToString(),
                            val = iReader["POSNAME"].ToString()
                        };
                        lstPos.Add(msPos);
                    }
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return lstPos;
        }

        public string GetPosNamesByIds(string posIds, string separator)
        {
            if (string.IsNullOrEmpty(posIds)) return string.Empty;

            string cmdText = "SELECT SYS_A3_20 AS POSNAME FROM SYS_A3 WHERE SYS_A3_10 IN (" + posIds + ")";
           
            string posNames = string.Empty;
            try {
                string posName = string.Empty;
                using (IDataReader iReader = DataAccessor.ExecuteReader(cmdText))
                {
                    while (iReader.Read())
                    {
                        posName = iReader["POSNAME"].ToString();
                        posNames += (string.IsNullOrEmpty(posNames)) ? posName : separator + posName;
                    }
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + cmdText + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }
            return posNames;
        }

        public string GetPosNamesByIds(string posIds, string separator, eTrueFalse isFilter)
        {
            if (string.IsNullOrEmpty(posIds)) return string.Empty;

            string posNames = string.Empty;
            if (isFilter == eTrueFalse.TRUE)
            {
                string cmdText = "SELECT SYS_A3_20 AS POSNAME FROM SYS_A3 WHERE SYS_A3_10 IN (" + posIds + ") AND SYS_A3_60='0'";
                   
                try
                {
                    string posName = string.Empty;
                    using (IDataReader iReader = DataAccessor.ExecuteReader(cmdText))
                    {
                        while (iReader.Read())
                        {
                            posName = iReader["POSNAME"].ToString();
                            posNames += (string.IsNullOrEmpty(posNames)) ? posName : separator + posName;
                        }
                    }
                }
                catch (Exception ex)
                {
                    MsErrLog msErrorLog = new MsErrLog
                    {
                        funcPageName = _funcPageName,
                        errMsg = ex.Message,
                        codeTrack = ex.StackTrace,
                        errClassCode = "001", //SQL 异常
                        paramInfo = "<SQL><![CDATA[" + cmdText + "]]></SQL>"
                    };

                    _logInterface.WriteErrorLog(msErrorLog);
                    msErrorLog = null;
                }
            }
            else
            {
                posNames = GetPosNamesByIds(posIds, separator);
            }
            return posNames;
        }

        public List<string> Delete(string posId)
        {
            List<string> lstCmdText = new List<string>();
            string cmdText = string.Empty;

            cmdText = "UPDATE SYS_A3 SET SYS_A3_60='1' WHERE SYS_A3_10='" + posId + "'";
            lstCmdText.Add(cmdText);

            //删除用户职位关系
            cmdText = "DELETE FROM SYS_R2 WHERE SYS_R2_20='" + posId + "'";
            lstCmdText.Add(cmdText);

            //删除职位机构关系
            cmdText = "DELETE FROM SYS_R6 WHERE SYS_R6_20='" + posId + "'";
            lstCmdText.Add(cmdText);

            try {
                int iexe = DataAccessor.ExecuteNonQuery(lstCmdText);
                if (iexe > 0) return new List<string> { "1", "删除成功!" };
                else return new List<string> { "-1", "操作失败:服务器忙请稍后再试!" };
            }
            catch (Exception ex)
            {
                StringBuilder sbParam = new StringBuilder();
                int sqlIndex = 1;
                foreach (string eSQL in lstCmdText)
                {
                    sbParam.Append("<SQL" + sqlIndex.ToString()).Append("><![CDATA[").Append(eSQL);
                    sbParam.Append("]]></SQL" + sqlIndex.ToString() + ">");
                }
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = sbParam.ToString()
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                sbParam = null;
                return new List<string> { "-2", "操作失败:详情请参见错误日志!" };
            }
        }

        public MsPos GetPosInfo(string posId)
        {
            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT A.SYS_A3_10 AS POSID,A.SYS_A3_20 AS POSNAME,A.SYS_A3_30 AS POSCODE,A.SYS_A3_40 AS POSDESC,");
            sbCmdText.Append("A.SYS_A3_50 AS ORDERNO,A.SYS_A3_60 AS EFFECT,");
            sbCmdText.Append("D.SYS_A4_10 AS ORGID,D.SYS_A4_20 AS ORGNAME,D.SYS_A4_30 AS ORGCODE,D.SYS_A4_40 AS ORGLAYCODE,");
            sbCmdText.Append("D.SYS_A4_50 AS ORGDESC,D.SYS_A4_60 AS ORGORDERNO,D.SYS_A4_70 AS ORGEFFECTSIGN ");
            sbCmdText.Append("FROM SYS_A3 A ");
            sbCmdText.Append("INNER JOIN SYS_R6 C ON A.SYS_A3_10=C.SYS_R6_20 ");
            sbCmdText.Append("INNER JOIN SYS_A4 D ON C.SYS_R6_10=D.SYS_A4_10 ");
            sbCmdText.Append("WHERE A.SYS_A3_10='").Append(posId).Append("'");

            MsPos msPos = null;
            try {
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read() && null != iReader[0])
                    {
                        msPos = new MsPos {
                            posId = iReader["POSID"].ToString(),
                            posName = iReader["POSNAME"].ToString(),
                            posCode = iReader["POSCODE"].ToString(),
                            posDesc = iReader["POSDESC"].ToString(),
                            orderNo = iReader["ORDERNO"].ToString(),
                            delSign = (eTrueFalse)Enum.Parse(typeof(eTrueFalse),iReader["EFFECT"].ToString()),
                            belongOrgan = new MsOrgan {
                                organId = iReader["ORGID"].ToString(),
                                organName = iReader["ORGNAME"].ToString(),
                                organCode = iReader["ORGCODE"].ToString(),
                                layCode = iReader["ORGLAYCODE"].ToString(),
                                organDesc = iReader["ORGDESC"].ToString(),
                                orderNo = iReader["ORGORDERNO"].ToString(),
                                effectSign = iReader["ORGEFFECTSIGN"].ToString()
                            }
                        };
                    }
                }

                sbCmdText.Remove(0, sbCmdText.ToString().Length);
                if (null == msPos) return null;

                //获取职位下用户信息
                sbCmdText.Append("SELECT SYS_A1_10 AS USERID,SYS_A1_40 AS USERNAME FROM SYS_A1 WHERE SYS_A1_10 IN(");
                sbCmdText.Append("SELECT SYS_R2_10 FROM SYS_R2 WHERE SYS_R2_20='").Append(posId).Append("') AND SYS_A1_90='0'");
                string userId = string.Empty;
                string userIds = string.Empty;
                string userName = string.Empty;
                string userNames = string.Empty;
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        userId = iReader["USERID"].ToString();
                        userName = iReader["USERNAME"].ToString();
                        userIds += (string.IsNullOrEmpty(userIds)) ? userId : "," + userId;
                        userNames += (string.IsNullOrEmpty(userName)) ? userName : " " + userName;
                    }
                }

                msPos.includeUsers = new MsKeyValue {
                    key = userIds,
                    val = userNames
                };

            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return msPos;
        }

        public List<string> Save(MsPos msPos)
        {
            string posId = msPos.posId;
            eEffect multiDept = (eEffect)Enum.Parse(typeof(eEffect), _multiDeptSign);
            if (string.IsNullOrEmpty(posId)) return addnew(msPos,multiDept);
            else return update(msPos,multiDept);
        }

        public List<string> IsExistPosName(string posId, string posName, string organId)
        {
            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT SYS_A3_10 AS POSID FROM SYS_A3 WHERE SYS_A3_20='");
            sbCmdText.Append(posName.Replace("'","''")).Append("' AND SYS_A3_10 IN(SELECT SYS_R6_20 FROM SYS_R6 ");
            sbCmdText.Append("WHERE SYS_R6_10='").Append(organId).Append("')");

            try {
                string posRecId = string.Empty;
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read() && null != iReader[0])
                    {
                        posRecId = iReader["POSID"].ToString();
                    }
                }

                if (string.IsNullOrEmpty(posRecId)) return new List<string> { "NOEXIST", "职位不存在!" };
                else
                {
                    if (posRecId.Equals(posId)) return new List<string> { "NOEXIST", "职位不存在!" };
                    else return new List<string> { "EXIST", "职位已存在!" };
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                return new List<string> { "ERROR", "校验失败:详情请参见错误日志!" };
            }
        }
        #endregion


        #region private methods

        private List<string> addnew(MsPos msPos,eEffect multiDept)
        {
            List<string> lstCmdText = new List<string>();
            StringBuilder sbCmdText = new StringBuilder();
            string cmdText = string.Empty;

            #region 保存职位记录信息

            sbCmdText.Append("INSERT INTO SYS_A3 (SYS_A3_10,SYS_A3_20,SYS_A3_30,SYS_A3_40,SYS_A3_50,SYS_A3_60) VALUES (");
            string newOrderNo = (string.IsNullOrEmpty(msPos.orderNo)) ? DataInterface.GetMaxId("SYS_A3","SYS_A3_50").ToString() : msPos.orderNo;
            int newPosId = DataInterface.GetMaxId("SYS_A3", "SYS_A3_10");
            sbCmdText.Append("'").Append(newPosId.ToString()).Append("',");
            sbCmdText.Append("'").Append(msPos.posName.Replace("'", "''")).Append("',");
            sbCmdText.Append("'").Append(msPos.posCode.Replace("'", "''")).Append("',");
            sbCmdText.Append("'").Append(msPos.posDesc.Replace("'", "''")).Append("',");
            sbCmdText.Append("'").Append(newOrderNo).Append("','0')");
            lstCmdText.Add(sbCmdText.ToString());
            sbCmdText.Remove(0, sbCmdText.ToString().Length);

            #endregion

            #region 保存职位用户关系

            string userIds = msPos.includeUsers.key;
            if (!string.IsNullOrEmpty(userIds))
            {
                //判断系统多部门、多职位标志.
                if (multiDept == eEffect.NO) //如果未启用多部门、多职位。则需要删除用户原先的职位关系
                {
                    cmdText = "DELETE FROM SYS_R2 WHERE SYS_R2_10 IN (" + userIds + ")";
                    lstCmdText.Add(cmdText);
                }
                string[] userArray = userIds.Split(',');
                foreach (string eUserId in userArray)
                {
                    cmdText = "INSERT INTO SYS_R2(SYS_R2_10,SYS_R2_20,SYS_R2_30) VALUES(";
                    cmdText += "'" + eUserId + "','" + newPosId.ToString() + "','0')";
                    lstCmdText.Add(cmdText);
                }
            }
            #endregion

            #region 保存职位机构关系
            string organId = msPos.belongOrgan.organId;
            cmdText = "INSERT INTO SYS_R6(SYS_R6_10,SYS_R6_20) VALUES(";
            cmdText += "'" + organId + "','" + newPosId.ToString() + "')";
            lstCmdText.Add(cmdText);
            #endregion

            try {
                int iexe = DataAccessor.ExecuteNonQuery(lstCmdText);
                if (iexe > 0) return new List<string> { newPosId.ToString(), "保存成功!" };
                else return new List<string> { "-1", "保存失败:服务器忙请稍后重新尝试操作!" };
            }
            catch (Exception ex)
            {
                StringBuilder sbParam = new StringBuilder();
                int sqlIndex = 1;
                foreach (string eSQL in lstCmdText)
                {
                    sbParam.Append("<SQL" + sqlIndex.ToString()).Append("><![CDATA[").Append(eSQL);
                    sbParam.Append("]]></SQL" + sqlIndex.ToString() + ">");
                }
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = sbParam.ToString()
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                sbParam = null;
                return new List<string> { "-2", "操作失败:详情请参见错误日志!" };
            }

        }

        private List<string> update(MsPos msPos, eEffect multiDept)
        {
            List<string> lstCmdText = new List<string>();
            string cmdText = string.Empty;
            string posId = msPos.posId;

            #region 更新职位记录信息

            cmdText = "UPDATE SYS_A3 SET SYS_A3_20='" + msPos.posName.Replace("'","''") + "'";
            cmdText += ",SYS_A3_30='" + msPos.posCode.Replace("'", "''") + "'";
            cmdText += ",SYS_A3_40='" + msPos.posDesc.Replace("'", "''") + "'";
            if (!string.IsNullOrEmpty(msPos.orderNo))
                cmdText += ",SYS_A3_50='" + msPos.orderNo + "'";
            cmdText += " WHERE SYS_A3_10='" + posId + "'";
            lstCmdText.Add(cmdText);
            #endregion

            #region 更新用户职位关系
            cmdText = "DELETE FROM SYS_R2 WHERE SYS_R2_20='" + posId + "'";
            lstCmdText.Add(cmdText);

            string userIds = msPos.includeUsers.key;
            if (!string.IsNullOrEmpty(userIds))
            {
                //判断系统多部门、多职位标志.
                if (multiDept == eEffect.NO) //如果未启用多部门、多职位。则需要删除用户原先的职位关系
                {
                    cmdText = "DELETE FROM SYS_R2 WHERE SYS_R2_10 IN (" + userIds + ")";
                    lstCmdText.Add(cmdText);
                }
                string[] userArray = userIds.Split(',');
                foreach (string eUserId in userArray)
                {
                    cmdText = "INSERT INTO SYS_R2(SYS_R2_10,SYS_R2_20,SYS_R2_30) VALUES(";
                    cmdText += "'" + eUserId + "','" + posId + "','0')";
                    lstCmdText.Add(cmdText);
                }
            }

            #endregion

            try
            {
                int iexe = DataAccessor.ExecuteNonQuery(lstCmdText);
                if (iexe > 0) return new List<string> { posId, "保存成功!" };
                else return new List<string> { "-1", "保存失败:服务器忙请稍后重新尝试操作!" };
            }
            catch (Exception ex)
            {
                StringBuilder sbParam = new StringBuilder();
                int sqlIndex = 1;
                foreach (string eSQL in lstCmdText)
                {
                    sbParam.Append("<SQL" + sqlIndex.ToString()).Append("><![CDATA[").Append(eSQL);
                    sbParam.Append("]]></SQL" + sqlIndex.ToString() + ">");
                }
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = sbParam.ToString()
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                sbParam = null;
                return new List<string> { "-2", "操作失败:详情请参见错误日志!" };
            }


        }
        #endregion

    }
}
